<?php
 /******************************************************************************
 *   GunCMS is an avid supporter of open source software. This is the appropriate 
 *   option if you are creating an open source application with a license 
 *   compatible with the GNU GPL license v3.
 *   For details http://www.gnu.org/licenses/gpl.html
 *   DO NOT USE FOR COMMERCIAL WITHOUT PERMISSION
 *
 * GunCMS Version 1.0.3
 * Copyright(c) 2009-2010, T & L GdbR
 * info@guncms.de
 * 
 * http://www.guncms.de
 ********************************************************************************/
class BgcmsPageBean {
	
	public function __construct() {
	}
		
	public function __destruct() {
		//
	}
	
	public function get_all_page($db){
    	$sql = sprintf("SELECT %1\$s.page_id,%1\$s.page_name, %3\$s.page_id as active_page,%3\$s.category_id, %4\$s.cat_name FROM %1\$s ". 
    			"LEFT JOIN %2\$s on %1\$s.page_author = %2\$s.user_id ".
				"LEFT JOIN %3\$s on %1\$s.page_id = %3\$s.page_id ".
				"LEFT JOIN %4\$s on %3\$s.category_id = %4\$s.cat_id ",
    			TBL_PAGE,
    			TBL_USER,
    			TBL_PAGE2CAT,
    			TBL_CATEGORY);
    	$res = $db->get_results($sql);
		
    	return $res;
    }
	
	public function save_assign_page($db,$pageid,$catid){
    	$res = $db->query(sprintf("INSERT INTO %s" .
									"(page_id, category_id) " .
									"VALUES(%d, %d)",
			    				TBL_PAGE2CAT, $pageid, $catid));
    }
	
	public function update_assign_page($db,$pageid,$catid){
		
		$res = $db->query(sprintf("UPDATE  ".TBL_PAGE2CAT." SET page_id =".$pageid." WHERE category_id = ".$catid));
    	
    }
	
	public function count_all_page($db, $keyword,$cat, $locale) {
	
		$cond = "";
		$where ="";
		$lang ="";
		if($keyword != "" || $locale != "" || $cat!='')
			$where = " WHERE ";
    	if($keyword != "") {
    		$keyword = str_replace("%", "%%", $keyword);
    		$cond = " AND " . TBL_PAGE . ".page_name like '%%$keyword%%'";
    	}
		if($cat != "") {
    		$str = " AND %3\$s.category_id='$cat' ";
    	}
    	if($locale != "") {
    		$lang = " %1\$s.lang_id='$locale' ";
    	}
		$sql = sprintf("SELECT COUNT(%1\$s.page_id) as total FROM %1\$s ". 
    			"LEFT JOIN %2\$s on %1\$s.page_author = %2\$s.user_id ".
				"LEFT JOIN %3\$s on %1\$s.page_id = %3\$s.page_id ".
				"LEFT JOIN %4\$s on %3\$s.category_id = %4\$s.cat_id ".
				"$where $lang $str $cond ", 
    			TBL_PAGE,
    			TBL_USER,
    			TBL_PAGE2CAT,
    			TBL_CATEGORY);
    	$res = $db->get_row($sql);
		return	$res->total;
	}
    public function get_all_page_paging($db, $keyword,$cat, $locale,$paging,$path) {
	
    	$cond = "";
		$where ="";
		$lang ="";
		if($keyword != "" || $locale != "" || $cat!='')
			$where = " WHERE ";
    	if($keyword != "") {
    		$keyword = str_replace("%", "%%", $keyword);
    		$cond = " AND " . TBL_PAGE . ".page_name like '%%$keyword%%'";
    	}
		if($cat != ""  ) {
    		$str = " AND %3\$s.category_id='$cat' ";
    	}
    	if($locale != "") {
    		$lang = " %1\$s.lang_id='$locale' ";
    	}
		
    	$page = isset ( $_GET[$path]) ? intval ( $_GET[$path] ) : 1;
		$rows_per_page= $paging;
		$page_start = ( $page - 1 ) * $rows_per_page;
		$page_end = $page * $rows_per_page;
    	$sql = sprintf("SELECT %1\$s.*, %2\$s.user_name, %4\$s.cat_name FROM %1\$s ". 
    			"LEFT JOIN %2\$s on %1\$s.page_author = %2\$s.user_id ".
				"LEFT JOIN %3\$s on %1\$s.page_id = %3\$s.page_id ".
				"LEFT JOIN %4\$s on %3\$s.category_id = %4\$s.cat_id ".
				"$where $lang $str $cond LIMIT $page_start,$rows_per_page ", 
    			TBL_PAGE,
    			TBL_USER,
    			TBL_PAGE2CAT,
    			TBL_CATEGORY);
    	$res = $db->get_results($sql);
		
    	return $res;
    }
    
    public static function save_page($db, $page) {
    	$res = $db->query(sprintf("INSERT INTO %s" .
					"(page_name, page_content,page_author," .
					"page_date, page_modified, lang_id) " .
					"VALUES('%s', '%s', '%s'," .
					"now(), now(),'%s')",
    				TBL_PAGE, 
    				$page['page_name'],
    				$page['page_content'],
    				$_SESSION['user_id'],
    				$_SESSION["LC_ALL"],
    				$page['page_type']));
    	
		if($res == true) {
    		$newId = $db->insert_id;
    		return $newId;
    	} else
    		return -1;
    }
    public function get_all_page_by_condition($db, $cond) {
    	$locale = $_SESSION["LC_ALL"];
    	$sql = sprintf("SELECT * FROM %1\$s LEFT JOIN %2\$s " .
    			"ON %1\$s.page_author = %2\$s.user_id WHERE lang_id='$locale' AND $cond", 
    			TBL_PAGE,
    			TBL_USER);
    	$res = $db->get_results($sql);
    	return $res;
    }
    
	public function get_page_by_id($db, $id, $locale) {
    	$page = null;
    	$sql = sprintf("SELECT * FROM %1\$s WHERE %1\$s.page_id=$id AND lang_id='$locale'", TBL_PAGE);
    	$page = $db->get_row($sql);
    	return $page;
    }
    
	public function get_page2cat_by_id($db,$catid, $id) {
    	$sql = sprintf("SELECT * FROM %1\$s WHERE %1\$s.page_id='$id' AND %1\$s.category_id='$catid'", TBL_PAGE2CAT);
    	$page = $db->get_row($sql);
    	return $page;
    }
	
	
	public function get_page2cat_by_catid($db,$catid) {
    	$sql = sprintf("SELECT * FROM %1\$s WHERE %1\$s.category_id='$catid'", TBL_PAGE2CAT);
    	$page = $db->get_row($sql);
    	return $page;
    }
	
    public function get_page_id_from_page2cat($db, $cat) {
    	$sql = "";
    	if($cat == "0") {
			$sql = 'SELECT page_id FROM ' . TBL_PAGE2CAT;
		} else {	
			$sql = sprintf("SELECT page_id FROM %s WHERE category_id={$cat}", TBL_PAGE2CAT);
		}
    	$res = $db->get_results($sql);
    	if($res != null) {
    		$s = '';
    		foreach($res as $obj) {
    			$s .= (empty($s)?'':',') . "'$obj->page_id'";
    		}
    		return $s;
    	}
    	return  '';
    }
    
  	public function get_all_page_by_cat($db, $cat, $userid) {
    	$cond = "";
    	if($cat != "") {
    		$p2c = BgcmsPageBean::get_page_id_from_page2cat($db, $cat);
    		if($p2c == '') return null;
    		//If Category Id = 0 we reverd SQL
    		if($cat == "0") {
    			$cond = sprintf("%1\$s.page_id NOT IN ($p2c)", TBL_PAGE);
    		} else {	
    			$cond = sprintf("%1\$s.page_id IN ($p2c)", TBL_PAGE);
    		}
    	}
    	
    	$userCond = "";
    	if($userid != null) {
    		if($cond != "")
    			$cond =  "(" . $cond . ") AND page_author = " . $userid;
    		else
    			$cond =  "page_author = " . $userid;
    	}
    	
    	$locale = $_SESSION["LC_ALL"];
    	
    	$sql = sprintf("SELECT * FROM %1\$s LEFT JOIN %2\$s " .
    			"ON %1\$s.page_author = %2\$s.user_id WHERE lang_id='$locale' AND ($cond)", 
    			TBL_PAGE, TBL_USER);
    	$res = $db->get_results($sql);
    	return $res;
    }
    
	public static function delete_page($db, $id) {
		//Delete Category
    	$sql = sprintf("DELETE FROM %1\$s " .
				"WHERE %1\$s.page_id = '$id'", TBL_PAGE);
    	$res = $db->query($sql);
    	if($res > 0) {
    		//Update pages that have page parent equal to this id
    		$sql = sprintf("UPDATE  %1\$s SET page_parent=0 WHERE page_parent = " . $id, TBL_PAGE);
			$db->query($sql);
    		$sql = sprintf("DELETE FROM %1\$s " .
					"WHERE %1\$s.page_id = $id", TBL_PAGE2CAT);
    		$db->query($sql);
    	}
    	return $res;
	}
	
	public static function update_page($db, $page) {
		$id = $_SESSION["user_id"];
    	$res = $db->query(sprintf("UPDATE " . TBL_PAGE . " SET " .
					"page_name 		= '%s', " .
					"page_content 	= '%s', " .
					"page_author	= '%s'," .
					"page_modified	= now() " .
					"WHERE page_id = '%s' ",
					($page['page_name']),
					($page['page_content']),
					($id),
					($page['id'])));
			
     	
    	if($res == true) {
    		return 0;
    	} else
    		return -1;
    }
}
?>